Reconciling Ledgers & Diagnosing Out of Balances

Process Definition: Reconciliation Reports: Accounts Receivable Accounts Payables AP Suspense Inventory Diagnosing Out of Balances Inventory Common Causes Researching the difference Still out of Balance?

Guidance to ensure ledgers and sub-ledgers are in balance and tips for diagnosing out of balances.

Process Definition:

At least once a month, it is important to ensure the following ledgers and sub-ledgers are in balance. See below sub-sections for the procedures.

• Accounts Receivables
• Accounts Payables
• AP Suspense (received not invoiced)
• Inventory

There are reconciliation reports for Accounts Receivable, Accounts Payable and Inventory in accounting>accounting reporting. These reports can be run using historical dates. The AP suspense reconciliation cannot be run historically.  

Reconciliation Reports:

Accounts Receivable

Confirm that the GL AR accounts have the same totals as the AR Summary report. To do this, generate an Accounting AR Reconciliation report for the correct dates and accounts. Be sure to select the correct Start and End Accounts for the reports to ensure AR is being compared to AR. This report is used to track when the AR subledger went out of balance by comparing the GL Value to the AR Value, calculating a Difference between the two, and calculating a Delta, the daily difference.  Lines with a value in the Delta column indicate that the account went out of balance on that date. The security setting "Accounting -- view AR Reconciliation" controls the ability to see and use this report.

To run this report:

1. Navigate to Accounting > Accounting Reporting
2. Report type is A/R Reconciliation.
3. Enter start date
4. Enter end date
5. Enter start account
6. Enter end account
7. Click View

Note:  This report can take a few minutes to run, be cognizant of the date range you are using. If differences occur, the Receivables summary (sub ledger) is considered more accurate.

Accounts Payables

Confirm that the GL AP accounts have the same totals as the AP Summary report. To do this, generate an Accounting Reporting AP Reconciliation report for the correct dates and accounts. Be sure to select the correct Start and End Accounts for the reports to ensure AP is being compared to AP. This report is used to track when the AP subledger went out of balance by comparing the GL Value to the AP Value, calculating a Difference between the two, and calculating a Delta, the daily difference.  Lines with a value in the Delta column indicate that the account went out of balance on that date. The security setting "Accounting -- view AP Reconciliation" controls the ability to see and use this report.

To run this report:

1. Navigate to Accounting > Accounting Reporting
2. Report type is A/P Reconciliation.
3. Enter start date
4. Enter end date
5. Enter start account
6. Enter end account
7. Click View

Note:  This report can take a few minutes to run, be cognizant of the date range you are using. If differences occur, the Receivables summary (sub ledger) is considered more accurate.

AP Suspense

If using an AP Suspense account, confirm the total of received not invoiced material matches what is in the AP Suspense account. This is difficult to reconcile as of a historical date, so performing this reconciliation on the correct date (last day of the month) is the best practice.

To run this report:

1. Navigate to Purchasing > Order Reporting
2. Select a "Status" of "Received Not Invoiced"
3. "Date Based On" of "Received"
4. Click "View". Ensure the Balance at the top of the resulting report matches the value in the AP Suspense account by running a general ledger summary.
   1. Navigate to Accounting > Accounting Reporting
   2. Report type is General Ledger Summary
   3. Enter start date
   4. Enter end date
   5. Enter Start account
   6. Enter end account
   7. Click view

Inventory

Confirm that the GL Inventory accounts have the same totals as the Inventory Lots report. To do this, generate an Accounting Inventory Reconciliation report for the correct dates and accounts. This report can reduce the time needed to balance the Inventory Sub-Ledger to the General Ledger. The report tracks changes and imbalances in the Inventory Sub-ledger by comparing the value from the Trial Balance report to the total value of on hand inventory.   This report is used to track when the Inventory subledger went out of balance by comparing the GL Value to the Lots Value (excluding WIP), calculating a Difference between the two, and calculating a Delta, the daily difference.  Lines with a value in the Delta column indicate that the account went out of balance on that date. The security setting "Accounting -- View Inventory Reconciliation" enables users to see and select the report in the "Report type" field on the pre-filter.

It is very common to have small differences when reconciling inventory.  The Lots report (subledger) often has values out to 4 decimal places, but the general ledger is only at 2 decimal places, so there can be decimal dust differences.   It is recommended that at least once a quarter, these rounding differences be adjusted in the general ledger to bring the reconciliations in balance. 

To run this report:

1. Navigate to Accounting > Accounting Reporting
2. Report type is Inventory Reconciliation.
3. Enter start date
4. Enter end date
5. Enter start account
6. Enter end account
7. Click View

Note:  It is recommended that you run each inventory account separately. 

This report can take a few minutes to run, be cognizant of the date range you are using. If differences occur, the Receivables summary (sub ledger) is considered more accurate.   

Diagnosing Out of Balances

Inventory

The general ledger and sub ledgers are designed to stay in balance, however on rare occasions they will go out of balance. 

Common Causes

• Back dated transactions
• “Un” transactions
  • Un invoicing
  • Un shipping
  • Un receiving

Researching the difference

1. Verify you are running the right report, with the correct accounts.
   1. i.e., if running an A/R Reconciliation report ensure you have entered AR accounts numbers.
2. Find the last time the general ledger and sub ledgers were in balance by running reconciliation reports for a longer time period.
3. Compare the ledger transactions to the sub ledger transactions.
   1. Look for entries that match the difference.
   2. Look for manual journal entries that were booked directly to the ledger account.  These entries do not flow to the sub ledgers.
4. Are they multiple posting for the same transaction?
   1. There should not be.
5. Look for unusual transactions such as post references that are not usually in the ledger account.
   1. i.e., a PJ (purchase order transactions) should not be hitting the AR account.

Still out of Balance?

A simple, yet effective solution when determining out of balance issues is the use of date range searches and account number comparisons. For example, comparing the ledger and sub-ledger week by week or even day by day can often result in the out of balance being narrowed down clearly. Assume an AP reconciliation is being performed and there is a mismatch between the GL and the AP values on January 29. To determine why the two reports are out of balance, run the Payables Summary report for January 28 and 29. compare the two reports to determine what balances changed. Then, run the GL Detail report for February 14 and determine which entry did not hit the GL. 

1. Run Payables for the day before the out of balance –January 28 in the above example.
   1. Export to excel.
2. Run Payables Summary for the day of out of balance – January 29 in the above example.
   1. Export to excel.
3. Run the general ledger detail for the day of the out of balance – January 29 in the above example.
   1. Export to excel.
4. Compare the 2 payables summary reports to find the change in balance by vendor.
5. Compare the variance (change in balance) to what was posted to the general ledger.

Reconciliation Worksheet

Company	1/28/2021	Company	1/29/2021	Variance	GL Activity	Variance
A Duie Pyle	$36,044.97	A Duie Pyle	$25,785.03	$10,259.94	$10,259.94	$        -
A Gargiule & Sons	$55.50			$55.50	$55.50	$        -
A1 Lumber and Mills	$10.02			$10.02	$10.02	$        -
ABC Inc.	$51,005.00	ABC, Inc.	$1,000.00	$50,005.00	$50,005.00	$        -
Acct Operations Vendor	$50,000.00	Acct Operations Vendor	$50,000.00	$               -	$            -	$        -
AccuStuff Corporation	$1,037.00	AccuStuff Corporation	$992.95	$44.05	$            -	$44.05
AG Devices of Colorado	$8,000.00			$8,000.00	$8,000.00	$        -
	$146,152.49		$77,777.98	$68,374.51	$68,363.46	$44.05

 Once the transaction(s) have been identified, determine the fix.  It could be as simple as a journal entry.  Many times, an External Program will be required to fix the out of balance issue. Contact Tech    Support if this is the case.